﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
using System.Data;
using System.Drawing;

namespace WpfApplication2
{
    class ExcelOperation
    {
        public Microsoft.Office.Interop.Excel.Application CreateExcel()
        {
            Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
            return xlApp;
        }

        public Workbook CreateWorkBook(Microsoft.Office.Interop.Excel.Application xlApp)
        {
            Workbook wb = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
            return wb;
        }

        public Worksheet CreateWorkSheet(Workbook wb, string Name)
        {
            try
            {
                Microsoft.Office.Interop.Excel.Sheets sheets = wb.Sheets;
                Microsoft.Office.Interop.Excel.Worksheet ws = (Worksheet)sheets.Add();
                ws.Name = "DummySheetNiryat";
                ((Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets["Sheet1"]).Delete();
                ws = (Worksheet)sheets.Add();
                ws.Name = Name;
                ((Microsoft.Office.Interop.Excel.Worksheet)wb.Sheets["DummySheetNiryat"]).Delete();
                return ws;
            }
            catch (Exception ex)
            {
                
                throw ex;
            }
            
        }

        public void WriteExcel(Worksheet ws, System.Data.DataTable dataTable)
        {
            try
            {
                throw new Exception();
                var columns = dataTable.Columns.Count;
                var rows = dataTable.Rows.Count + 1;

                Microsoft.Office.Interop.Excel.Range range = ws.Range["A1", String.Format("{0}{1}", GetExcelColumnName(columns), rows)];

                object[,] data = new object[rows, columns];

                int Rowcount = 0;
                foreach (DataRow dataRow in dataTable.Rows)
                {
                    int ColumnCount = 0;
                    if (Rowcount >= 1)
                        break;
                    foreach (DataColumn dataColumn in dataTable.Columns)
                    {
                        data[Rowcount, ColumnCount] = dataColumn.ColumnName.ToString(); ;
                        ColumnCount++;
                    }
                    Rowcount++;
                }

                for (int rowNumber = 1; rowNumber < rows; rowNumber++)
                {
                    for (int columnNumber = 0; columnNumber < columns; columnNumber++)
                    {
                        data[rowNumber, columnNumber] = dataTable.Rows[rowNumber - 1][columnNumber].ToString();
                    }
                }

                range.Value = data;
                range.Columns.AutoFit();
                range.Borders.Value = 1;
                Microsoft.Office.Interop.Excel.Range NewRange = ws.Range["A1", String.Format("{0}{1}", GetExcelColumnName(columns), 1)];
                NewRange.Font.Bold = true;
                NewRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Silver);
                NewRange.Application.ActiveWindow.SplitRow = 1;
                NewRange.Application.ActiveWindow.FreezePanes = true;
            }
            catch (Exception ex)
            {
                
                throw ex;
            }
           
        }

        public void SaveExcel(Workbook wb,string ExcelName)
        {
            
            wb.SaveAs(ExcelName);
            wb.Close();
        }
        
        private static string GetExcelColumnName(int columnNumber)
        {
            int dividend = columnNumber;
            string columnName = String.Empty;
            int modulo;

            while (dividend > 0)
            {
                modulo = (dividend - 1) % 26;
                columnName = Convert.ToChar(65 + modulo).ToString() + columnName;
                dividend = (int)((dividend - modulo) / 26);
            }

            return columnName;
        }
    }
}
